#!/usr/bin/env python
# -*- coding: utf-8 -*-

import datetime
import math

from django.db import connection
from django.db.models import Q
from typing import List
from web.util.datetime_util import DatetimeUtil
from web.dao.base_dao import BaseDao
from web.models.commodity_future_date_data import CommodityFutureDateData
from web.constants.datetime_format import DatetimeFormat
from web.util.math_util import MathUtil


class CommodityFutureDateDataDao(BaseDao):
    """
    CommodityFutureDataData的dao类
    """

    model_class = CommodityFutureDateData

    def add_oracle_column_comment(self):
        """
        为表commodity_future_date_data添加注释
        """

        with connection.cursor() as cursor:
            cursor.execute("comment on table commodity_future_date_data is \'商品期货交易数据表（日线级别）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.id IS \'主键\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.code IS \'代码\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.transaction_date IS \'日期\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.name IS \'名称\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.highest_price IS \'最高价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.lowest_price IS \'最低价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.open_price IS \'开盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.close_price IS \'最新价/收盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.last_close_price IS \'上个交易日收盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.volume IS \'成交额\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.turnover IS \'成交量\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.open_interest IS \'持仓量\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.buying IS \'买盘（外盘）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.selling IS \'卖盘（内盘）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.rising_and_falling_amount IS \'涨跌额\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.price_change IS \'涨跌幅\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma5 IS \'5日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma10 IS \'10日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma20 IS \'20日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma60 IS \'60日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma120 IS \'120日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ma250 IS \'250日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ema12 IS \'MACD的ema12\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.ema26 IS \'MACD的ema26\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.dif IS \'MACD的dif\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.dea IS \'MACD的dea\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.rsv IS \'KD的rsv\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.k IS \'KD的k\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.d IS \'KD的d\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_date_data.create_time IS \'创建时间\'")

    ########################################### 全量计算数据 ########################################

    def write_date_basic_data(self):
        """
        计算日线级别全部last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_BASIC_DATA()")

    def write_date_all_ma(self):
        """
        计算日线级别全部MA数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_FIVE()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_TEN()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_TWENTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_SIXTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_ONEHUNDREDTWENTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CALCULATE_TWOHUNDREDFIFTY()")

    def write_date_all_macd(self):
        """
        计算日线级别全部MACD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_INIT()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_EMA()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_DIF()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_DEA()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD()")

    def write_date_all_kd(self):
        """
        计算日线级别全部KD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_INIT()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_RSV()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_K()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_D()")

    def write_date_all_boll(self):
        """
        计算日线级别全部boll数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CAL_BOLL()")

    def write_date_all_hei_kin_ashi(self):
        """
        计算日线级别全部hei_kin_ashi数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_HA()")

    def write_date_all_bias(self):
        """
        计算日线级别全部bias数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_BIAS()")

    def write_date_all_variance(self):
        """
        计算日线级别全部variance数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_VARIANCE()")

    def write_date_unite_relative_price_index(self):
        """
        计算日线级别全部unite_relative_price_index数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_DATE_DATA.CAL_UNITE_RELATIVE_PRICE_INDEX()")

    ########################################### 增量计算数据 ########################################
    def write_date_basic_data_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_BASIC_DATA_BY_DATE", [date])

    def write_date_unite_relative_price_index_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的unite_relative_price_index数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.CAL_U_R_PRICE_INDEX_BY_DATE", [date])

    def write_date_ma_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的MA数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MOVING_AVERAGE_BY_DATE", [date])

    def write_date_macd_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的MACD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_EMA_BY_DATE", [date])
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_DIF_BY_DATE", [date])
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_DEA_BY_DATE", [date])
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_MACD_BY_DATE", [date])

    def write_date_kd_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的KD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_BY_DATE_RSV", [date])
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_BY_DATE_K", [date])
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_KD_BY_DATE_D", [date])

    def write_date_hei_kin_ashi_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的hei kin ashi数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_HA_BY_DATE", [date])

    def write_date_variance_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的variance数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_VARIANCE_BY_DATE", [date])

    def write_date_boll_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的BOLL数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.CAL_BOLL_BY_DATE", [date])

    def write_date_bias_by_date(self, date):
        """
        根据日期，计算日线级别某一个交易日的BIAS数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_DATE_DATA.WRITE_BIAS_BY_DATE", [date])

    ########################################### robot ########################################

    def find_distinct_transaction_date_between_transaction_date_order_by_transaction_date(self, begin_date, end_date):
        """
        查找开始时间和结束时间之间的日期，并去重
        """

        with connection.cursor() as cursor:
            cursor.execute("select distinct t.transaction_date from commodity_future_date_data t "
                           "where t.transaction_date between %s and %s "
                           "order by t.transaction_date asc",
                           (DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format)))
            transaction_date_tuple = cursor.fetchall()
            return transaction_date_tuple

        # query = Q(transaction_date__gte=DatetimeUtil.str_to_datetime(Robot8Config.Begin_Date, DatetimeFormat.Date_Format)) & Q(
        #     transaction_date__lte=DatetimeUtil.str_to_datetime(Robot8Config.End_Date, DatetimeFormat.Date_Format))
        # commodity_future_date_data_query_set = self.model_class.objects.distinct("transaction_date").filter(query).order_by("transaction_date").values_list('transaction_date', flat=True)
        # return commodity_future_date_data_query_set

    def find_list_by_code_order_by_transaction_date_asc(self, code) -> list:
        """
        根据code，查找某一种期货的全部交易记录，并按照transaction_date升序排列
        """

        filter_dict = {'code': code}
        order_by_list = ['transaction_date']
        return self.find_list(filter_dict, dict(), order_by_list)

    ########################################### 创建图片 ########################################
    def find_four_strategy_gold_cross_rate_between_group_by_order_by_transaction_date(self, begin_date, end_date):
        """
        根据开始日期和结束日期，计算每一天，四种算法金叉的比率
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.transaction_date, "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.dif>t1.dea)/"
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date)*100, "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.close_price>t1.ma5)/"
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date)*100, "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.ha_close_price>t1.ha_open_price)/"
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date)*100, "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.k>t1.d)/"
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date)*100 "
                           "from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "group by t.transaction_date order by t.transaction_date asc", (begin_date, end_date))
            four_strategy_gold_cross_rate_tuple = cursor.fetchall()
            return four_strategy_gold_cross_rate_tuple

    def find_average_four_strategy_gold_cross_rate_between_group_by_order_by_transaction_date(self, begin_date,
                                                                                              end_date):
        """
        根据开始日期和结束日期，计算每一天，四种算法金叉的比率的平均值
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.transaction_date, "
                           "((select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.dif>t1.dea) + "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.close_price>t1.ma5) + "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.ha_close_price>t1.ha_open_price) + "
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date and t1.k>t1.d))/4/"
                           "(select count(*) from commodity_future_date_data t1 where t1.transaction_date=t.transaction_date)*100 "
                           "from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "group by t.transaction_date order by t.transaction_date asc", (begin_date, end_date))
            average_four_strategy_gold_cross_rate_tuple = cursor.fetchall()
            return average_four_strategy_gold_cross_rate_tuple

    def find_backward_between_n_date(self, transaction_date, n):
        """
        根据transaction_date，向前查找第n个交易日的日期，n中包括transaction_date
        """

        with connection.cursor() as cursor:
            cursor.execute("select * from ("
                           "select distinct t.transaction_date "
                           "from commodity_future_date_data t "
                           "where t.transaction_date<=to_date(%s,'yyyy-mm-dd') order by t.transaction_date desc) "
                           "where rownum<=%s", (transaction_date, n))
            five_date_tuple = cursor.fetchall()
            return five_date_tuple[n - 1][0]

    def find_date_price_change_up_percentage_group_by_transaction_date(self, begin_date, end_date):
        """
        根据开始时间和结束时间，计算每一日上涨期货的百分比，并按时间升序排列
        """
        with connection.cursor() as cursor:
            cursor.execute("select t1.transaction_date, "
                           "(select count(*) from commodity_future_date_data t "
                           "where t.transaction_date=t1.transaction_date and t.price_change>0)/"
                           "(select count(*) from commodity_future_date_data t "
                           "where t.transaction_date=t1.transaction_date and t.price_change!=0) * 100 "
                           "from commodity_future_date_data t1 "
                           "where t1.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "group by t1.transaction_date "
                           "order by t1.transaction_date asc", (begin_date, end_date))
            price_change_up_percentage_tuple = cursor.fetchall()
            return price_change_up_percentage_tuple

    def backward_n_transaction_date(self, current_transaction_date, n_date):
        """
        查询某个交易日之前第n_date哥交易日是哪天
        """
        with connection.cursor() as cursor:
            cursor.execute("select transaction_date from ( "
                           "select * from ( "
                           "select * from ( "
                           "select * from unite_relative_price_index_d t "
                           "where t.transaction_date<=to_date(%s,'yyyy-mm-dd') "
                           "order by t.transaction_date desc) t1 where rownum<=%s) t2 "
                           "order by t2.transaction_date asc) "
                           "where rownum<=1", (current_transaction_date, n_date))
            backward_n_transaction_date_tuple = cursor.fetchall()
            return backward_n_transaction_date_tuple[0][0]

    def average_price_change_up_percentage_by_current_transaction_date(self, current_transaction_date, n_date):
        """
        查询某个交易日之后（包括这个交易日）最近n_date天的平均上涨期货的百分比
        """
        backward_n_transaction_date = self.backward_n_transaction_date(current_transaction_date, n_date)
        backward_n_transaction_date = DatetimeUtil.datetime_to_str(backward_n_transaction_date,
                                                                   DatetimeFormat.Date_Format_With_Line)

        with connection.cursor() as cursor:
            cursor.execute("select ( "
                           "select count(*)/%s from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "and t.price_change>0)/( "
                           "select count(*)/%s from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "and t.price_change!=0)*100 "
                           "from dual", (
                               n_date, backward_n_transaction_date, current_transaction_date, n_date,
                               backward_n_transaction_date, current_transaction_date))
            average_price_change_up_percentage_by_current_transaction_date_tuple = cursor.fetchall()
            return average_price_change_up_percentage_by_current_transaction_date_tuple[0][0]

    def find_date_price_change_up_percentage_between_transaction_date(self, begin_date, end_date):
        """
        根据开始时间和结束时间，计算某一周上涨家数百分比
        """
        with connection.cursor() as cursor:
            cursor.execute("select ("
                           "select count(*) from commodity_future_date_data t "
                           "where t.transaction_date>=to_date(%s,'yyyy-mm-dd') "
                           "and t.transaction_date<=to_date(%s,'yyyy-mm-dd') and t.price_change>0)/"
                           "(select count(*) from commodity_future_date_data t "
                           "where t.transaction_date>=to_date(%s,'yyyy-mm-dd') "
                           "and t.transaction_date<=to_date(%s,'yyyy-mm-dd') and t.price_change!=0)*100 "
                           "from dual", (begin_date, end_date, begin_date, end_date))
            percentage_tuple = cursor.fetchall()
            return percentage_tuple

    def average_price_change_up_percentage_between_transaction_date(self, begin_date, end_date, n_date):
        """
        根据开始时间和结束时间，计算这段时间的平均上涨家数
        """
        with connection.cursor() as cursor:
            cursor.execute("select ( "
                           "select count(*)/%s from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "and t.price_change>0)/( "
                           "select count(*)/%s from commodity_future_date_data t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "and t.price_change!=0)*100 "
                           "from dual", (n_date, begin_date, end_date, n_date, begin_date, end_date))
            average_price_change_up_percentage_between_transaction_date_tuple = cursor.fetchall()
            return average_price_change_up_percentage_between_transaction_date_tuple

    def find_average_kd_order_by_transaction_date_asc(self, transaction_date):
        """
        计算某个交易日的平均KD，并按照transaction_date升序排列
        """
        date = DatetimeUtil.datetime_to_str(transaction_date, DatetimeFormat.Date_Format_With_Line)
        sql = "select t.transaction_date, avg(t.k), avg(t.d) from commodity_future_date_data t " \
              "where t.transaction_date=to_date('{0}', 'yyyy-mm-dd') " \
              "group by t.transaction_date order by t.transaction_date asc".format(date)
        with connection.cursor() as cursor:
            cursor.execute(sql)
            average_kd_tuple = cursor.fetchall()
            return average_kd_tuple[0]

    def find_average_boll_order_by_transaction_date_asc(self, transaction_date):
        """
        计算某个交易日的平均BOLL，并按照transaction_date升序排列
        """
        date = DatetimeUtil.datetime_to_str(transaction_date, DatetimeFormat.Date_Format_With_Line)
        sql = "select t.transaction_date, avg(t.close_price), avg(t.up), avg(t.mb), avg(t.dn) from commodity_future_date_data t " \
              "where t.transaction_date=to_date('{0}', 'yyyy-mm-dd') " \
              "group by t.transaction_date order by t.transaction_date asc".format(date)
        with connection.cursor() as cursor:
            cursor.execute(sql)
            average_boll_tuple = cursor.fetchall()
            return average_boll_tuple[0]

    def prepare_for_hmm_with_few_eigenvalue(self, code: str, begin_date: datetime.date, end_date: datetime.date) -> ():
        """
        为hmm算法准备数据
        """

        one_day_log_turnover_difference_list: List[float] = list()
        five_day_log_turnover_difference_list: List[float] = list()
        one_day_log_close_price_difference_list: List[float] = list()
        five_day_log_close_price_difference_list: List[float] = list()
        five_day_log_highest_price_lowest_price_difference_list: List[float] = list()
        transaction_date_list: List[datetime] = list()
        close_price_list: List[float] = list()

        commodity_future_date_data_queryset = CommodityFutureDateData.objects.filter(
            Q(transaction_date__range=[begin_date, end_date]) & Q(code=code)).order_by('transaction_date')
        if commodity_future_date_data_queryset is not None and len(commodity_future_date_data_queryset) > 0:
            # 一日对数成交量差
            one_day_log_turnover_difference: float
            # 五日对数成交量差
            five_day_log_turnover_difference: float
            # 一日对数收盘价差
            one_day_log_close_price_difference: float
            # 五日对数收盘价差
            five_day_log_close_price_difference: float
            # 当日对数高低价差
            five_day_log_highest_price_lowest_price_difference: float

            for current_commodity_future_date_data in commodity_future_date_data_queryset:
                # 计算一日对数成交量差
                last_commodity_future_date_data = CommodityFutureDateData.objects.filter(
                    Q(transaction_date__lt=current_commodity_future_date_data.transaction_date) & Q(
                        code=code)).order_by(
                    "-transaction_date").first()
                one_day_log_turnover_difference = ((
                                                       0 if current_commodity_future_date_data.turnover == 0.0 else math.log(
                                                           current_commodity_future_date_data.turnover))
                                                   - (
                                                       0 if last_commodity_future_date_data.turnover == 0.0 else math.log(
                                                           last_commodity_future_date_data.turnover)))
                one_day_log_turnover_difference_list.append(one_day_log_turnover_difference)

                # 计算五日对数成交量差
                last_five_commodity_future_date_data = CommodityFutureDateData.objects.filter(
                    Q(transaction_date__lt=current_commodity_future_date_data.transaction_date) & Q(
                        code=code)).order_by(
                    "-transaction_date")[4:5].first()
                five_day_log_turnover_difference = (
                            (0 if current_commodity_future_date_data.turnover == 0.0 else math.log(
                                current_commodity_future_date_data.turnover))
                            - (0 if last_five_commodity_future_date_data.turnover == 0.0 else math.log(
                        last_five_commodity_future_date_data.turnover)))
                five_day_log_turnover_difference_list.append(five_day_log_turnover_difference)

                # 计算一日对数收盘价差
                one_day_log_close_price_difference = math.log(
                    current_commodity_future_date_data.close_price) - math.log(
                    last_commodity_future_date_data.close_price)
                one_day_log_close_price_difference_list.append(one_day_log_close_price_difference)

                # 计算五日对数收盘价差
                five_day_log_close_price_difference = math.log(
                    current_commodity_future_date_data.close_price) - math.log(
                    last_five_commodity_future_date_data.close_price)
                five_day_log_close_price_difference_list.append(five_day_log_close_price_difference)

                # 计算当日对数高低价差
                five_day_log_highest_price_lowest_price_difference = math.log(
                    current_commodity_future_date_data.highest_price) - math.log(
                    current_commodity_future_date_data.lowest_price)
                five_day_log_highest_price_lowest_price_difference_list.append(
                    five_day_log_highest_price_lowest_price_difference)

                # 日期
                transaction_date_list.append(current_commodity_future_date_data.transaction_date)

                # 收盘价
                close_price_list.append(current_commodity_future_date_data.close_price)

        return (one_day_log_turnover_difference_list, five_day_log_turnover_difference_list,
                one_day_log_close_price_difference_list, five_day_log_close_price_difference_list,
                five_day_log_highest_price_lowest_price_difference_list, transaction_date_list, close_price_list,
                len(commodity_future_date_data_queryset))

    def prepare_for_hmm_with_many_eigenvalue(self, code: str, begin_date: datetime.date, end_date: datetime.date) -> ():
        """
        为hmm算法准备数据
        """

        open_price_list: List[float] = list()
        close_price_list: List[float] = list()
        highest_price_list: List[float] = list()
        lowest_price_list: List[float] = list()
        turnover_list: List[float] = list()
        transaction_date_list: List[datetime] = list()
        # open_interest_list: List[float] = list()
        rising_and_falling_amount_list: List[float] = list()
        price_change_list: List[float] = list()
        ma5_list: List[float] = list()
        ma10_list: List[float] = list()
        ma20_list: List[float] = list()
        ma60_list: List[float] = list()
        ma120_list: List[float] = list()
        ma250_list: List[float] = list()
        ema12_list: List[float] = list()
        ema26_list: List[float] = list()
        dif_list: List[float] = list()
        dea_list: List[float] = list()
        rsv_list: List[float] = list()
        k_list: List[float] = list()
        d_list: List[float] = list()
        up_list: List[float] = list()
        mb_list: List[float] = list()
        dn_list: List[float] = list()
        ha_open_price_list: List[float] = list()
        ha_close_price_list: List[float] = list()
        ha_highest_price_list: List[float] = list()
        ha_lowest_price_list: List[float] = list()
        bias5_list: List[float] = list()
        bias10_list: List[float] = list()
        bias20_list: List[float] = list()
        bias60_list: List[float] = list()
        bias120_list: List[float] = list()
        bias250_list: List[float] = list()
        variance5_list: List[float] = list()
        variance10_list: List[float] = list()
        variance20_list: List[float] = list()
        variance60_list: List[float] = list()
        variance120_list: List[float] = list()
        variance250_list: List[float] = list()
        macd_list: List[float] = list()

        commodity_future_date_data_queryset = CommodityFutureDateData.objects.filter(
            Q(transaction_date__range=[begin_date, end_date]) & Q(code=code) & Q(open_interest__isnull=False) &
            Q(rising_and_falling_amount__isnull=False) & Q(price_change__isnull=False) &
            Q(ma5__isnull=False) & Q(ma10__isnull=False) & Q(ma20__isnull=False) & Q(ma60__isnull=False) &
            Q(ma120__isnull=False) & Q(ma250__isnull=False) & Q(ema12__isnull=False) & Q(ema26__isnull=False) &
            Q(dif__isnull=False) & Q(dea__isnull=False) & Q(rsv__isnull=False) & Q(k__isnull=False) &
            Q(d__isnull=False) & Q(up__isnull=False) & Q(mb__isnull=False) & Q(dn__isnull=False) &
            Q(ha_open_price__isnull=False) & Q(ha_close_price__isnull=False) & Q(ha_highest_price__isnull=False) &
            Q(ha_lowest_price__isnull=False) & Q(bias5__isnull=False) & Q(bias10__isnull=False) &
            Q(bias20__isnull=False) & Q(bias60__isnull=False) & Q(bias120__isnull=False) &
            Q(bias250__isnull=False) & Q(variance5__isnull=False) & Q(variance10__isnull=False) &
            Q(variance20__isnull=False) & Q(variance60__isnull=False) & Q(variance120__isnull=False) &
            Q(variance250__isnull=False) & Q(macd__isnull=False)).order_by('transaction_date')
        if commodity_future_date_data_queryset is not None and len(commodity_future_date_data_queryset) > 0:

            for current_commodity_future_date_data in commodity_future_date_data_queryset:
                # 查询前一个交易日的记录
                last_commodity_future_date_data = CommodityFutureDateData.objects.filter(
                    Q(transaction_date__lt=current_commodity_future_date_data.transaction_date) & Q(
                        code=code)).order_by("-transaction_date").first()

                # decimal转换为float
                open_price_list.append(float(last_commodity_future_date_data.open_price))
                close_price_list.append(float(last_commodity_future_date_data.close_price))
                highest_price_list.append(float(last_commodity_future_date_data.highest_price))
                lowest_price_list.append(float(last_commodity_future_date_data.lowest_price))
                turnover_list.append(float(last_commodity_future_date_data.turnover))
                transaction_date_list.append(last_commodity_future_date_data.transaction_date)
                # open_interest_list.append(float(last_commodity_future_date_data.open_interest))
                rising_and_falling_amount_list.append(float(last_commodity_future_date_data.rising_and_falling_amount))
                price_change_list.append(float(last_commodity_future_date_data.price_change))
                ma5_list.append(float(last_commodity_future_date_data.ma5))
                ma10_list.append(float(last_commodity_future_date_data.ma10))
                ma20_list.append(float(last_commodity_future_date_data.ma20))
                ma60_list.append(float(last_commodity_future_date_data.ma60))
                ma120_list.append(float(last_commodity_future_date_data.ma120))
                ma250_list.append(float(last_commodity_future_date_data.ma250))
                ema12_list.append(float(last_commodity_future_date_data.ema12))
                ema26_list.append(float(last_commodity_future_date_data.ema26))
                dif_list.append(float(last_commodity_future_date_data.dif))
                dea_list.append(float(last_commodity_future_date_data.dea))
                rsv_list.append(float(last_commodity_future_date_data.rsv))
                k_list.append(float(last_commodity_future_date_data.k))
                d_list.append(float(last_commodity_future_date_data.d))
                up_list.append(float(last_commodity_future_date_data.up))
                mb_list.append(float(last_commodity_future_date_data.mb))
                dn_list.append(float(last_commodity_future_date_data.dn))
                ha_open_price_list.append(float(last_commodity_future_date_data.ha_open_price))
                ha_close_price_list.append(float(last_commodity_future_date_data.ha_close_price))
                ha_highest_price_list.append(float(last_commodity_future_date_data.ha_highest_price))
                ha_lowest_price_list.append(float(last_commodity_future_date_data.ha_lowest_price))
                bias5_list.append(float(last_commodity_future_date_data.bias5))
                bias10_list.append(float(last_commodity_future_date_data.bias10))
                bias20_list.append(float(last_commodity_future_date_data.bias20))
                bias60_list.append(float(last_commodity_future_date_data.bias60))
                bias120_list.append(float(last_commodity_future_date_data.bias120))
                bias250_list.append(float(last_commodity_future_date_data.bias250))
                variance5_list.append(float(last_commodity_future_date_data.variance5))
                variance10_list.append(float(last_commodity_future_date_data.variance10))
                variance20_list.append(float(last_commodity_future_date_data.variance20))
                variance60_list.append(float(last_commodity_future_date_data.variance60))
                variance120_list.append(float(last_commodity_future_date_data.variance120))
                variance250_list.append(float(last_commodity_future_date_data.variance250))
                macd_list.append(float(last_commodity_future_date_data.macd))

        return (open_price_list,
                close_price_list,
                highest_price_list,
                lowest_price_list,
                turnover_list,
                transaction_date_list,
                # open_interest_list,
                rising_and_falling_amount_list,
                price_change_list,
                ma5_list,
                ma10_list,
                ma20_list,
                ma60_list,
                ma120_list,
                ma250_list,
                ema12_list,
                ema26_list,
                dif_list,
                dea_list,
                rsv_list,
                k_list,
                d_list,
                up_list,
                mb_list,
                dn_list,
                ha_open_price_list,
                ha_close_price_list,
                ha_highest_price_list,
                ha_lowest_price_list,
                bias5_list,
                bias10_list,
                bias20_list,
                bias60_list,
                bias120_list,
                bias250_list,
                variance5_list,
                variance10_list,
                variance20_list,
                variance60_list,
                variance120_list,
                variance250_list,
                macd_list,
                len(commodity_future_date_data_queryset))

    def find_by_code_between_transaction_date_order_by_transaction_date_asc(self, code, begin_date, end_date):
        """
        在一定时间范围内，某种主力合约的数据，并按日期升序排列
        """
        with connection.cursor() as cursor:
            cursor.execute("select * "
                           "from commodity_future_date_data t "
                           "where t.transaction_date between %s and %s "
                           "and t.code = %s "
                           "order by t.transaction_date asc",
                           (DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format),
                            code))
            _list = cursor.fetchall()
            return _list

    def find_date_and_close_price_by_code_between_transaction_date_order_by_transaction_date_asc(self, code, begin_date,
                                                                                                 end_date):
        """
        在一定时间范围内，某种主力合约的数据，并按日期升序排列
        """
        with connection.cursor() as cursor:
            cursor.execute("select t.transaction_date, t.close_price "
                           "from commodity_future_date_data t "
                           "where t.transaction_date between %s and %s "
                           "and t.code = %s "
                           "order by t.transaction_date asc",
                           (DatetimeUtil.str_to_datetime(begin_date, DatetimeFormat.Date_Format),
                            DatetimeUtil.str_to_datetime(end_date, DatetimeFormat.Date_Format),
                            code))
            _tuple = cursor.fetchall()
            return _tuple

    def find_max_and_min_transaction_date_by_code(self, code):
        """
        根据code，查询最大的和最小的transaction_date
        """

        with connection.cursor() as cursor:
            sql = ("select max(t.transaction_date), min(t.transaction_date) "
                       "from commodity_future_date_data t "
                       "where t.code = '{}'").format(code)
            cursor.execute(sql)
            _tuple = cursor.fetchall()
            return _tuple[0][0], _tuple[0][1]

    def find_max_variance20_60_120_by_code(self, code: str):
        """
        根据code查询最大的variance20、variance60和variance120
        """

        with connection.cursor() as cursor:
            sql = ("select max(t.variance20), max(t.variance60), max(t.variance120) "
                       "from commodity_future_date_data t "
                       "where t.code = '{}'").format(code)
            cursor.execute(sql)
            _tuple = cursor.fetchall()
            return _tuple[0][0], _tuple[0][1], _tuple[0][2]

    def find_average_variance20_60_120_by_code(self, code: str):
        """
        根据code查询variance20、variance60和variance120的平均值
        """

        with connection.cursor() as cursor:
            sql = ("select avg(t.variance20), avg(t.variance60), avg(t.variance120) "
                       "from commodity_future_date_data t "
                       "where t.code = '{}'").format(code)
            cursor.execute(sql)
            _tuple = cursor.fetchall()
            return _tuple[0][0], _tuple[0][1], _tuple[0][2]